package com.zxl.manager.privacy.utils.database;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * 数据库工具类(封装共有方法及操作)
 * 
 * 类名称：DatabaseUtils
 * 类描述：
 * 创建人：makai
 * 修改人：makai
 * 修改时间：2014年8月25日 上午10:51:17
 * 修改备注：
 * @version 1.0.0
 *
 */
public class DatabaseUtils {
	
	public final static String TYPE_BLOB = "blob";

	public final static String TYPE_TEXT = "text";
	
	public final static String TYPE_NUMERIC = "numeric";
	
	/**
	 * 检查指定的表是否存在
	 * @param db
	 * @param tableName	表名
	 * @return
	 */
	public static boolean isExistTable(final SQLiteDatabase db, String tableName) {
		boolean result = false;
		Cursor cursor = null;
		String where = "type='table' and name='" + tableName + "'";
		try {
			cursor = db.query("sqlite_master", null, where, null, null, null, null);
			if (cursor != null && cursor.getCount() > 0) {
				result = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return result;
	}
	
	/**
	 * 检查表中是否存在该字段
	 * @param db
	 * @param tableName		表名
	 * @param columnName	字段名
	 * @return
	 */
	public static boolean isExistColumnInTable(SQLiteDatabase db, String tableName, String columnName) {
		boolean result = false;
		Cursor cursor = null;
		try {
			// 查询列数
			String columns[] = { columnName };
			cursor = db.query(tableName, columns, null, null, null, null, null);
			if (cursor != null && cursor.getColumnIndex(columnName) >= 0) {
				result = true;
			}
		} catch (Exception e) {
			 e.printStackTrace();
			result = false;
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return result;
	}
	
	/**
	 * 新添加字段到表中
	 * @param db
	 * @param tableName			修改表名
	 * @param columnName		新增字段名
	 * @param columnType		新增字段类型
	 * @param defaultValue		新增字段默认值。为null，则不提供默认值
	 */
	public static void addColumnToTable(SQLiteDatabase db, String tableName, String columnName, String columnType, String defaultValue) {
		if (!isExistColumnInTable(db, tableName, columnName)) {
			db.beginTransaction();
			try {
				// 增加字段
				String updateSql = "ALTER TABLE " + tableName + " ADD " + columnName + " " + columnType;
				db.execSQL(updateSql);
				// 提供默认值
				if (defaultValue != null) {
					if (columnType.equals(TYPE_TEXT)) {
						// 如果是字符串类型，则需加单引号
						defaultValue = "'" + defaultValue + "'";
					}
					updateSql = "update " + tableName + " set " + columnName + " = " + defaultValue;
					db.execSQL(updateSql);
				}
				db.setTransactionSuccessful();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (null != db) {
					try {
						db.endTransaction();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		}
	}
	
	public static boolean execSQL(SQLiteOpenHelper sqLiteOpenHelper, String... sql) {
		return execSQL(sqLiteOpenHelper.getWritableDatabase(), sql);
	}

	public static boolean execSQL(SQLiteDatabase database, String... sql) {
		boolean result = false;
		if (sql == null || sql.length == 0) {
			return result;
		}
		try {
			database.beginTransaction();
			for (String string : sql) {
				database.execSQL(string);
			}
			database.setTransactionSuccessful();
			result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			database.endTransaction();
		}
		return result;
	}

	public static boolean execSQL(SQLiteOpenHelper sqLiteOpenHelper, Object... sql) {
		return execSQL(sqLiteOpenHelper.getWritableDatabase(), sql);
	}

	public static boolean execSQL(SQLiteDatabase database, Object... sql) {
		boolean result = false;
		if (sql == null || sql.length == 0) {
			return result;
		}
		try {
			database.beginTransaction();
			for (Object value : sql) {
				if (null == value) {
					continue;
				}
				if (value instanceof String) {
					database.execSQL(value.toString());
				} else if (value instanceof UpdateParams) {
					UpdateParams updateParams = (UpdateParams) value;
					database.update(updateParams.table, updateParams.contentValues, updateParams.where, null);
				} else if (value instanceof InsertParams) {
					InsertParams insertParams = (InsertParams) value;
					database.insert(insertParams.table, null, insertParams.contentValues);
				}

			}
			database.setTransactionSuccessful();
			result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			database.endTransaction();
		}
		return result;
	}

	public static Cursor rawQuery(SQLiteOpenHelper sqLiteOpenHelper, String sql, String[] selectionArgs) {
		Cursor result = null;
		try {
			SQLiteDatabase db = sqLiteOpenHelper.getReadableDatabase();
			result = db.rawQuery(sql, selectionArgs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalStateException e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 更新参数
	 */
	public static class UpdateParams {

		public ContentValues contentValues;

		public String where;

		public String table;

		public UpdateParams(ContentValues contentValues, String where, String table) {
			this.contentValues = contentValues;
			this.where = where;
			this.table = table;
		}

		@Override
		public String toString() {
			return "UpdateParams[" + table + ", " + contentValues + ", " + where + "]";
		}
	}

	/**
	 * 插入参数
	 */
	public static class InsertParams {

		public ContentValues contentValues;

		public String table;

		public InsertParams(ContentValues contentValues, String table) {
			this.contentValues = contentValues;
			this.table = table;
		}

		@Override
		public String toString() {
			return "InsertParams[" + table + ", " + contentValues + "]";
		}
	}

}
